import openpyxl
from openpyxl import load_workbook
from ConfigFile.Varconfig import testdata_path


class ExcelOprate:

    def __init__(self):
        self.workbook = None
        self.sheet = None

    def load_workbook(self,filename):
        '''
        加载文件
        :param filename:
        :return:
        '''
        try:
            self.workbook = load_workbook(filename)
            print(self.workbook.sheetnames)
        except Exception as e:
            print(e)

    def get_sheet_by_name(self,sheetname):
        '''
        获取指定sheet页
        :param sheetname:
        :return:
        '''
        try:
            self.sheet = self.workbook[sheetname]
            # print(self.sheet.max_row)
        except Exception as e:
            print(e)

    def get_rows_nums(self):
        '''
        获取行数
        :return:
        '''
        return self.sheet.max_row

    def get_cols_nums(self):
        '''
        获取列数
        :return:
        '''
        return self.sheet.max_column

    def get_row_values(self,row):
        '''
        获取某一行的值
        :param row:
        :return:
        '''
        try:
            columns = self.sheet.max_column
            row_values = []
            for i in range(1,columns+1):
                cell_value =self.sheet.cell(row=row,column=i).value
                row_values.append(cell_value)
            return row_values
        except Exception as e:
            print(e)

    def get_cell_values(self,row,column):
        '''
        获取某个单元格的值
        :param row:
        :param cloumn:
        :return:
        '''
        cell_values =self.sheet.cell(row=row,column=column).value
        return cell_values

if __name__ == '__main__':
    ex = ExcelOprate()
    ex.load_workbook(testdata_path)
    ex.get_sheet_by_name('Sheet1')
    print(ex.get_rows_nums())
    print(ex.get_cols_nums())
    print(ex.get_row_values(1))
    print(ex.get_cell_values(2,2))

